const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *入库审核接口
 */

//  入库开始时间
let startTime = '2019-04-08 00:00:00';
// let startTime = '';
//  获得订单信息

const getVerifyStorageOrder = async (ctx, next) => {
    const body = ctx.request.body;
    console.log(body);
    let publicSql = ` FROM RKD_ZB Z LEFT JOIN (SELECT ID,SUM(SJSL) AS SJSL FROM YPSJ GROUP BY ID) SJ ON Z.ID = SJ.ID LEFT JOIN RKD_JS J ON Z.ID = J.ID  LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  
    WHERE Z.SH= 'F' AND Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}' `;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,CASE WHEN Z.ZSL<=SJ.SJSL THEN 'T' ELSE 'F' END AS STATUS,J.XH,
    Z.RQ,Z.ID,Z.DWBM,Z.BMBM,Z.BMMC,Z.DWMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.JBRMC,Z.FKQX,Z.PJLX,Z.PJHM,Z.PZH,Z.ZJS,Z.ZSS,Z.ZHJ,J.KM1BM,J.KM2BM,
    J.KM3BM,J.KMQC ${publicSql}`;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL  ${publicSql} `
    let actionSqlList = [];
    if (body.searchVal.JSFSBM) {
        actionSqlList.push(` AND Z.JSFSBM='${body.searchVal.JSFSBM}'`)
    }
    if (body.searchVal.GYS) {
        actionSqlList.push(` AND (B.BM LIKE '%${body.searchVal.GYS}%' OR B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%') `);
    }
    if (body.searchVal.isUp) {
        actionSqlList.push(` AND ${body.searchVal.isUp === 'T' ? 'Z.ZSL<=SJ.SJSL' : 'Z.ZSL>SJ.SJSL'} `);
    }

    startSql += ` ${actionSqlList.join(' ')}) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    totalSql += actionSqlList.join(' ');
    console.log(startSql);
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}

//  获得订单对应的商品信息
const getVerifyStorageOrderShop = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT CASE WHEN ISNULL(J.SJSL,0) = 0 THEN 'F' ELSE 'T' END AS ISUP,CASE WHEN M.SL<=J.SJSL THEN 'T' ELSE 'F' END AS STATUS,M.YPBM,M.YPMC,M.YPGG,M.CKBM,M.CKMC,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,M.SL,M.JE,M.SCPH,M.SCRQ,M.YXQZ,M.HWBH,Y.CCTJ,Y.MJSL,M.JS,M.SS,
    M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,M.HJ,Y.JG_FLAG,Y.YXFL,Y.JP,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ,L.LSDJ,(T1.DDSL + T2.DQSL) AS DDSL,Y.SSXK 
    FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID LEFT JOIN BM_YP Y ON M.YPBM =  Y.BM 
    LEFT JOIN (SELECT  MIN(YPPHGZ) AS YPPHGZ,YPBM FROM YPPHGZ GROUP BY YPBM) G ON M.YPBM = G.YPBM 
    LEFT JOIN (SELECT MIN(DJ) AS LSDJ,YPBM FROM RKD_MX GROUP BY YPBM)L ON M.YPBM = L.YPBM 
    LEFT JOIN (SELECT DX.ID,DX.YPBM,(DX.DDSL-ISNULL(SUM(RX.RMSL),0)) AS DDSL FROM 
    (SELECT SUM(SL) AS DDSL,ID,YPBM FROM DHD_MX GROUP BY ID,YPBM) DX 
    LEFT JOIN RKD_ZB RZ ON DX.ID = RZ.ID_DHD 
    LEFT JOIN (SELECT SL AS RMSL,ID,YPBM FROM RKD_MX ) RX  ON RZ.ID = RX.ID AND DX.YPBM = RX.YPBM   GROUP BY DX.ID,DX.YPBM,DX.DDSL) 
    T1 ON Z.ID_DHD = T1.ID AND M.YPBM = T1.YPBM LEFT JOIN (SELECT SUM(SL) AS DQSL,ID,YPBM FROM RKD_MX GROUP BY ID,YPBM) T2 ON M.ID = T2.ID AND M.YPBM = T2.YPBM  
    LEFT JOIN (SELECT ID,YPBM,SCPH,SUM(SJSL) AS SJSL FROM YPSJ GROUP BY ID,YPBM,SCPH) J ON M.ID = J.ID AND M.YPBM = J.YPBM AND M.SCPH = J.SCPH WHERE M.ID = '${body.orderId}' `
    let result = await sql.query(startSql);
    ctx.body = {
        data: result.recordset
    };
}

// 补全时间
const repairTime = (time) => {
    return time.toString().length == 1 ? ('0' + time) : time;
}
// 获取时间拼接的年月日
const getNowTimeId = (time) => {
    let nowTime = time ? new Date(time) : new Date();
    let y = nowTime.getFullYear();
    let M = nowTime.getMonth() + 1;
    let d = nowTime.getDate();
    let h = nowTime.getHours();
    let m = nowTime.getMinutes();
    let s = nowTime.getSeconds();
    return {
        msDate: repairTime(m) + repairTime(s),
        date: y.toString().slice(2) + repairTime(M) + repairTime(d),
        fullDate: `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(h)}:${repairTime(m)}:${repairTime(s)}.000`
    }
}
// 获得有效期至
// const getValidityTimeTo = (m) => {
//     let timeStamp = new Date().getTime();
//     let trasformStamp = m * 86400000;
//     let validityTime = new Date(timeStamp + trasformStamp);
//     return getNowTimeId(validityTime).fullDate
// }

let transformTimeToDay = ({ SCRQ, YXQZ }) => {
    let scrqStamp = new Date(SCRQ).getTime();
    let yxqzStamp = new Date(YXQZ).getTime();
    return Math.round((yxqzStamp - scrqStamp) / 86400000)
}
//  保存入库审核的订单信息
const keepVerifyStorage = async (ctx, next) => {
    try {
        const body = ctx.request.body;
        let orderList = body.chooseOrderList;
        let shopList = body.entryList;
        let queryList = body.queryList;
        let actionSqlList = [];


        let zje = 0, zss = 0, zjs = 0, zsl = 0;
        // 删除入库明细表
        actionSqlList.push(`DELETE FROM RKD_MX WHERE ID = '${orderList.ID}'`);
        // 插入入库明细表
        let insertMxSql = `INSERT INTO RKD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,
    YXQX,YXQZ,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,JE,RT,SE,HJ,ZKJE,CB,JG,FLAG,CKBM,CKMC,HWBH,
    YXFL,BZ,PFJ,LSJ,HYJ,UID) `;

        for (let ind in shopList) {
            let item = shopList[ind];
            //更新规则
            let isHavePHGZSql = `SELECT COUNT(YPBM) AS SL FROM YPPHGZ WHERE YPBM = '${item.YPBM}'`;
            let isHavePHGZ = await sql.query(isHavePHGZSql);
            if (isHavePHGZ.recordset[0].SL === 0) {
                actionSqlList.push(`INSERT INTO YPPHGZ VALUES ('${item.YPBM}','${item.YPPHGZ}')`);
            } else {
                actionSqlList.push(`UPDATE YPPHGZ SET YPPHGZ = '${item.YPPHGZ}' WHERE YPBM = '${item.YPBM}'`);
            }
            actionSqlList.push(`UPDATE BM_YP SET YXQX = '${transformTimeToDay({ SCRQ: item.SCRQ, YXQZ: item.YXQZ })}' WHERE BM= '${item.YPBM}'`);

            // 插入入库明细表的数据
            let dj = Number(item.DJ ? item.DJ : 0);
            let sl = Number(item.SL ? item.SL : 0);
            let mjsl = Number(item.MJSL ? item.MJSL : 0);
            let je = dj * sl;
            let js = mjsl === 0 ? 0 : Math.floor(sl / mjsl);
            let ss = sl - (mjsl * js);
            zje += je;
            zss += ss;
            zjs += js;
            zsl += sl;
            let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);
            insertMxSql += `SELECT '${orderList.ID}',${getNowTimeId().msDate + xhSerialNum},${ind + 1},
            '${item.YPBM}','${item.YPTM}','${item.YPMC}','${item.YPGG}','${item.JXMC}','${item.SCCJ}',
            '${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}',
            '${item.JLDW}',${item.MJSL},${item.SL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},
            ${item.DJ_HS},${je},0,0,${je},0,'A','${item.JG_FLAG}','','${item.CKBM}','${item.CKMC}',
            '${item.HWBH.toUpperCase()}','${item.YXFL}','',${item.PFJ},${item.LSJ},${item.HYJ},'' `;
            if (Number(ind) !== shopList.length - 1) {
                insertMxSql += ` UNION `
            }

            // 更新库存动态表

            // let kcslResult = await sql.query(`SELECT KCSL,KYSL,CBJJ,KCJE,SCRQ,YXQX,YXQZ FROM KC_KCDTB WHERE BMBM ='${orderList.BMBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}'`);
            // let kcInfo = kcslResult.recordset[0];
            // // 如果有库存记录
            // if (kcslResult.rowsAffected[0] > 0) {
            //     let kcsl = kcInfo.KCSL ? Number(kcInfo.KCSL) : 0;
            //     let kysl = kcInfo.KYSL ? Number(kcInfo.KYSL) : 0;
            //     let kcje = kcInfo.KCJE ? Number(kcInfo.KCJE) : 0;
            //     let cbjj = (kcsl || sl) ? ((je + kcje) / (kcsl + sl)).toFixed(2) : 0;
            //     let newKcje = (cbjj * (kcsl + sl)).toFixed(2);
            //     let updateKcSql = `UPDATE KC_KCDTB SET RKRQ = '${baseFn.getNowTimeId().fullDate}',SCRQ = '${item.SCRQ}',YXQX = ${item.YXQX},YXQZ = '${item.YXQZ}',
            //     KCSL = ${kcsl + sl},KYSL = ${kysl + sl},HWBH = '${item.HWBH}',DWBM = '${orderList.DWBM}',DWMC = '${orderList.DWMC}',CBJJ=${cbjj},KCJE=${newKcje} 
            //     WHERE BMBM ='${orderList.BMBM}' AND YPBM = '${item.YPBM}' AND SCPH = '${item.SCPH}' AND CKBM = '${item.CKBM}' `;
            //     actionSqlList.push(updateKcSql);
            // } else {
            //     // 如果没有库存记录
            //     let insertKcSql = `INSERT INTO KC_KCDTB(BMBM,CKBM,CKMC,YPBM,SCPH,RKRQ,SCRQ,YXQX,YXQZ,KCSL,
            // KYSL,CBJJ,KCJE,DWBM,DWMC,HWBH,YHRQ) VALUES ('${orderList.BMBM}','${item.CKBM}',
            // '${item.CKMC}','${item.YPBM}','${item.SCPH}','${baseFn.getNowTimeId().fullDate}',
            // '${item.SCRQ}',${item.YXQX},'${item.YXQZ}',${item.SL},${item.SL},${item.DJ},${je},
            // '${orderList.DWBM}','${orderList.DWMC}','${item.HWBH}','')`;
            //     actionSqlList.push(insertKcSql);
            // }
        };
        // 插入明细表
        actionSqlList.push(insertMxSql);
        // 更新总表
        let updateZbSql = `UPDATE RKD_ZB SET SHR = '${queryList.SHR}',PJLX = '${queryList.PJLX}',
        PJHM= '${queryList.PJHM}',ZSL = ${zsl},ZJS = ${zjs},ZSS = ${zss},ZJE = ${zje},ZHJ = ${zje},
        YJS = ${zje},WJS = ${zje},SH='T' WHERE ID = '${orderList.ID}'`;
        actionSqlList.push(updateZbSql);
        // 更新结算表
        let updateJsSql = `UPDATE RKD_JS SET JSJE = ${zje} WHERE ID = '${orderList.ID}'`;
        actionSqlList.push(updateJsSql);

        //往来单位金额的计算
        let dwInfoResult = await sql.query(`SELECT YINFYE,WLYE FROM BM_WLDW WHERE BM = '${orderList.DWBM}'`);
        let dwInfo = dwInfoResult.recordset[0];
        let yinfye = Number(dwInfo.YINFYE ? dwInfo.YINFYE : 0) + zje;
        let wlyf = Number(dwInfo.WLYE ? dwInfo.WLYE : 0) - zje;
        actionSqlList.push(`UPDATE BM_WLDW SET YINFYE = ${yinfye},WLYE = ${wlyf} WHERE BM = '${orderList.DWBM}'`);
        let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')} Commit Tran `;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        ctx.body = { success: false, err }
    }

}






module.exports = {
    'POST /getVerifyStorageOrder': getVerifyStorageOrder,
    'POST /getVerifyStorageOrderShop': getVerifyStorageOrderShop,
    'POST /keepVerifyStorage': keepVerifyStorage
}
